import sqlite3
from datetime import datetime

class DatabaseManager:
    DATABASE_PATH = 'forum_base.db'

    # 用户登录验证账号密码
    def login(self, username, password):
        with sqlite3.connect(self.DATABASE_PATH) as conn:
            cursor = conn.cursor()
            try:
                cursor.execute("SELECT password FROM users WHERE username = ?", (username,))
                result = cursor.fetchone()
                if result:
                    return result[0] == password
                return False
            except sqlite3.Error as e:
                print(f"数据库操作错误: {e}")
                return False

    # 账号注册
    def register(self, username, password):
        with sqlite3.connect(self.DATABASE_PATH) as conn:
            cursor = conn.cursor()
            try:
                cursor.execute("INSERT INTO users (username, password) VALUES (?, ?)", (username, password))
                conn.commit()
                return True
            except sqlite3.Error as e:
                print(f"数据库操作错误: {e}")
                return False

    # 按照post_time顺序查询所有帖子        
    def fetch_posts(self):
        with sqlite3.connect(self.DATABASE_PATH) as conn:
            cursor = conn.cursor()
            try:
                cursor.execute("SELECT * FROM posts ORDER BY post_time DESC")
                posts = cursor.fetchall()
                return posts
            except sqlite3.Error as e:
                print(f"数据库操作错误: {e}")

    # 添加新帖子信息
    def add_post(self, user_name, post_title, post_content, post_time, user_avatar=None, num_replies=0, num_likes=0):
        with sqlite3.connect(self.DATABASE_PATH) as conn:
            cursor = conn.cursor()
            try:
                cursor.execute("INSERT INTO posts (user_name, post_title, post_content, post_time, user_avatar, num_replies, num_likes) VALUES (?, ?, ?, ?, ?, ?, ?)",
                                (user_name, post_title, post_content, post_time, user_avatar, num_replies, num_likes))
                conn.commit()
                print("帖子添加成功")
            except sqlite3.Error as e:
                print(f"数据库操作错误: {e}")

    # 点赞action=1是添加这个赞，=0是取消这个赞
    def add_or_delete_post(self, user_name, post_title, action):
        with sqlite3.connect(self.DATABASE_PATH) as conn:
            cursor = conn.cursor()
            try:
                # 添加或取消点赞
                if action == 1:
                    # 向posts_skr表添加点赞
                    cursor.execute("INSERT INTO posts_skr (user_name, post_title, post_SKR) VALUES (?, ?, ?)",
                                   (user_name, post_title, 1))
                    # 在posts表中增加点赞数
                    cursor.execute("UPDATE posts SET num_likes = num_likes + 1 WHERE post_title = ?", (post_title,))
                    print("点赞成功")
                elif action == 0:
                    # 从posts_skr表删除点赞
                    cursor.execute("DELETE FROM posts_skr WHERE user_name = ? AND post_title = ?",
                                   (user_name, post_title))
                    # 在posts表中减少点赞数
                    cursor.execute("UPDATE posts SET num_likes = num_likes - 1 WHERE post_title = ?", (post_title,))
                    print("取消点赞成功")
                conn.commit()
            except sqlite3.Error as e:
                print(f"数据库操作错误: {e}")
                return False
            return True


    # 查看点赞数量
    # 检查特定用户的特定帖子是否存在
    def check_post_existence(self, post_title, username):
        with sqlite3.connect(self.DATABASE_PATH) as conn:
            cursor = conn.cursor()
            try:
                # 执行查询，检查帖子是否存在
                cursor.execute("SELECT * FROM posts_skr WHERE post_title = ? AND user_name = ?", (post_title, username))
                result = cursor.fetchone()
                if result:
                    return 0  # 如果找到记录，返回0
                else:
                    return 1  # 如果没有找到记录，返回1
            except sqlite3.Error as e:
                # 输出错误信息并返回1表示查询失败
                print(f"数据库操作错误: {e}")
                return 1

    #获取回复
    def fetch_replies_by_title(self, post_title):
        with sqlite3.connect(self.DATABASE_PATH) as conn:
            cursor = conn.cursor()
            try:
                cursor.execute("""
                    SELECT user_name, post_title, post_huifu_text 
                    FROM posts_huifu 
                    WHERE post_title = ?
                    """, (post_title,))
                replies = cursor.fetchall()
                
                # 打印查询到的所有回复
                for reply in replies:
                    print("Post Name:", reply[0], "| Reply Text:", reply[2])
                
                # 返回查询到的结果
                return replies
            
            except sqlite3.Error as e:
                print(f"数据库操作错误: {e}")
                return None

    #添加回复
    def add_reply_to_post(self, user_name, post_title, reply_text):
        with sqlite3.connect(self.DATABASE_PATH) as conn:
            cursor = conn.cursor()
            try:
                cursor.execute("""
                    INSERT INTO posts_huifu (user_name, post_title, post_huifu_text) 
                    VALUES (?, ?, ?)
                    """, (user_name, post_title, reply_text))
                
                # 在posts表中增加回复数
                print(post_title)
                cursor.execute("UPDATE posts SET num_replies = num_replies + 1 WHERE post_title = ?", (post_title,))
                rows_updated = cursor.rowcount  # 获取影响的行数

                if rows_updated == 0:
                    print("没有找到对应的帖子标题，未更新回复数量")
                else:
                    print(f"更新了{rows_updated}行的回复数量")
                
                conn.commit()
                print("回复添加成功")
                return True
            except sqlite3.Error as e:
                print(f"数据库操作错误: {e}")
                return False


        
    

    

# 测试登录功能
#db_manager = DatabaseManager()
# 假设有一个用户名为'user123'，密码为'password123'的用户
#is_logged_in = db_manager.login("123", "123")
#print("登录成功" if is_logged_in else "登录失败")

# 尝试注册新用户'user456'，密码为'password456'
#is_registered = db_manager.register("user456", "password456")
#print("注册成功" if is_registered else "注册失败")

# 调用函数获取并打印帖子信息
#db_manager.fetch_posts()

# 示例用法，添加一个新帖子
# 示例用法，添加一个新帖子，用户头像留空，时间格式化为年月日，时分
#formatted_time = datetime.now().strftime("%Y-%m-%d %H:%M")
#db_manager.add_post("user123", "新帖子标题", "这是一个新帖子的内容", formatted_time)


# 示例用法，点赞
#db_manager.add_or_delete_post("用户3", "有趣的帖子2", 1)
# 示例用法，取消赞
#db_manager.add_or_delete_post("user123", "有趣的帖子", 0)


# 示例用法，查询一个帖子的点赞数量
#likes = db_manager.count_likes("有趣的帖子")

# try:
#     db_manager.add_or_delete_post("用户3", "有趣的帖子2", 1)
# except Exception as e:
#     print(f"操作失败，错误信息：{str(e)}")

#db_manager.fetch_replies_by_title("qweqweq")
##result = db_manager.add_reply_to_post("aisdo","强强强强强强强强强强", "45464465")
##if result:
##    print("回复已成功添加")
##else:
##    print("回复添加失败")
